package com.shsh.safemanage.dao;

import java.io.ByteArrayOutputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import com.shsh.safemanage.model.HSECategory;
import com.shsh.safemanage.model.HSECategoryCause;
import com.shsh.safemanage.model.HSEMonthCompareReport;
import com.shsh.safemanage.model.HSEMonthDetailReport;
import com.shsh.safemanage.model.HSEReportUnit;
import com.shsh.safemanage.model.ProblemHSECategory;
import com.shsh.safemanage.util.Constants;
import com.shsh.safemanage.util.ValueUtil;

public class HSEProblemDao {
	public List<HSECategory> getHSECategoryList(Integer categoryType) {
		String sql = "select ID, CATEGORY_NAME, CATEGORY_TYPE, DIRECT_CAUSE_ID, REMOTE_CAUSE_ID, IS_OTHER from hse_category ";
		if (categoryType != null) {
			sql += "where CATEGORY_TYPE=? ";
		}
		sql += "order by ID";

		List<HSECategory> categoryList = new ArrayList<HSECategory>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			if (categoryType != null) {
				pstm.setInt(1, categoryType);
			}

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				HSECategory hseCategory = new HSECategory();
				hseCategory.setId(ValueUtil.getLong(rs.getObject("ID")));
				hseCategory.setCategoryName(ValueUtil.getString(rs.getObject("CATEGORY_NAME")));
				hseCategory.setCategoryType(ValueUtil.getInteger(rs.getObject("CATEGORY_TYPE")));
				hseCategory.setDirectCauseID(ValueUtil.getLong(rs.getObject("DIRECT_CAUSE_ID")));
				hseCategory.setRemoteCauseID(ValueUtil.getLong(rs.getObject("REMOTE_CAUSE_ID")));
				hseCategory.setOther(ValueUtil.getBoolean(rs.getObject("IS_OTHER")));

				categoryList.add(hseCategory);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return categoryList;
	}

	public HSECategory getHSECategoryByID(Long id) {
		String sql = "select ID, CATEGORY_NAME, CATEGORY_TYPE, DIRECT_CAUSE_ID, REMOTE_CAUSE_ID, IS_OTHER "
				+ "from hse_category where ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, id);
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				HSECategory hseCategory = new HSECategory();
				hseCategory.setId(ValueUtil.getLong(rs.getObject("ID")));
				hseCategory.setCategoryName(ValueUtil.getString(rs.getObject("CATEGORY_NAME")));
				hseCategory.setCategoryType(ValueUtil.getInteger(rs.getObject("CATEGORY_TYPE")));
				hseCategory.setDirectCauseID(ValueUtil.getLong(rs.getObject("DIRECT_CAUSE_ID")));
				hseCategory.setRemoteCauseID(ValueUtil.getLong(rs.getObject("REMOTE_CAUSE_ID")));
				hseCategory.setOther(ValueUtil.getBoolean(rs.getObject("IS_OTHER")));

				return hseCategory;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public boolean deleteProblemHSECategory(Long problemID) {
		String sql = "delete from problem_hse_category where PROBLEM_ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			conn.setAutoCommit(false);

			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, problemID);

			pstm.executeUpdate();
			conn.commit();

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public boolean addProblemHSECategory(ProblemHSECategory problemHSECategory) {
		String sql = "insert into problem_hse_category(PROBLEM_ID, HSE_CATEGORY_ID, HSE_CATEGORY_TYPE, DIRECT_CAUSE_ID, "
				+ "REMOTE_CAUSE_ID, OTHER) values(?, ?, ?, ?, ?, ?)";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, problemHSECategory.getProblemID());
			pstm.setLong(2, problemHSECategory.getHseCategoryID());
			pstm.setInt(3, problemHSECategory.getHseCategoryType());
			pstm.setLong(4, problemHSECategory.getDirectCauseID());
			pstm.setLong(5, problemHSECategory.getRemoteCauseID());
			pstm.setString(6, problemHSECategory.getOther());

			int count = pstm.executeUpdate();

			if (count > 0) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public List<ProblemHSECategory> getProblemHSECategoryList(Long problemID) {
		String sql = "select PROBLEM_ID, HSE_CATEGORY_ID, HSE_CATEGORY_TYPE, DIRECT_CAUSE_ID, REMOTE_CAUSE_ID, OTHER "
				+ "from problem_hse_category where PROBLEM_ID=?";

		List<ProblemHSECategory> problemHSECategoryList = new ArrayList<ProblemHSECategory>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, problemID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				ProblemHSECategory problemHSECategory = new ProblemHSECategory();
				problemHSECategory.setProblemID(ValueUtil.getLong(rs.getObject("PROBLEM_ID")));
				problemHSECategory.setHseCategoryID(ValueUtil.getLong(rs.getObject("HSE_CATEGORY_ID")));
				problemHSECategory.setHseCategoryType(ValueUtil.getInteger(rs.getObject("HSE_CATEGORY_TYPE")));
				problemHSECategory.setDirectCauseID(ValueUtil.getLong(rs.getObject("DIRECT_CAUSE_ID")));
				problemHSECategory.setRemoteCauseID(ValueUtil.getLong(rs.getObject("REMOTE_CAUSE_ID")));
				problemHSECategory.setOther(ValueUtil.getString(rs.getObject("OTHER")));

				problemHSECategoryList.add(problemHSECategory);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return problemHSECategoryList;
	}

	public boolean updateProblemHSECategory(ProblemHSECategory problemHSECategory) {
		String sql = "update problem_hse_category set HSE_CATEGORY_ID=?, DIRECT_CAUSE_ID=?, REMOTE_CAUSE_ID=?, "
				+ "OTHER=? where PROBLEM_ID=? and HSE_CATEGORY_TYPE=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, problemHSECategory.getHseCategoryID());
			pstm.setLong(2, problemHSECategory.getDirectCauseID());
			pstm.setLong(3, problemHSECategory.getRemoteCauseID());
			pstm.setString(4, problemHSECategory.getOther());
			pstm.setLong(5, problemHSECategory.getProblemID());
			pstm.setInt(6, problemHSECategory.getHseCategoryType());

			int count = pstm.executeUpdate();
			if (count > 0) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public void fullfillHSEDepartMonthDetailReportTable(HSEMonthDetailReport<HSEReportUnit> monthReport, String dateStart, String dateEnd,
			int directCauseType, List<Long> unitIDArray, List<Long> causeIDArray) {
		String tableSQL = "select hcc.ID CATEGORY_CAUSE_ID, p.EQUIPMENT_ID, COUNT(phc.PROBLEM_ID) PROBLEM_COUNT "
				+ " from problem_hse_category as phc inner join problem as p on p.ID = phc.PROBLEM_ID  " + " and p.WRITE_TIME>='"
				+ dateStart + " 00:00:00' and p.WRITE_TIME<='" + dateEnd + " 23:59:59' ";
		if (monthReport.getDepartment1ID() != null) {
			tableSQL += " and p.DEPARTMENT1_ID = " + monthReport.getDepartment1ID().longValue() + " ";
			if (monthReport.getEquipID() != null) {
				tableSQL += " and p.EQUIPMENT_ID = " + monthReport.getEquipID().longValue() + " ";
			}
		}
		tableSQL += " inner join hse_category_cause as hcc on hcc.ID = phc.DIRECT_CAUSE_ID and hcc.DIRECT_CAUSE_TYPE = " + directCauseType
				+ " group by hcc.ID, p.EQUIPMENT_ID";

		String dangerCountSQL = "select p.ID, COUNT(*) "
				+ " from problem_hse_category as phc inner join problem as p on p.ID = phc.PROBLEM_ID  " + " and p.WRITE_TIME>='"
				+ dateStart + " 00:00:00' and p.WRITE_TIME<='" + dateEnd + " 23:59:59' ";
		if (monthReport.getDepartment1ID() != null) {
			dangerCountSQL += " and p.DEPARTMENT1_ID = " + monthReport.getDepartment1ID().longValue() + " ";
			if (monthReport.getEquipID() != null) {
				dangerCountSQL += " and p.EQUIPMENT_ID = " + monthReport.getEquipID().longValue() + " ";
			}
		}
		dangerCountSQL += " inner join hse_category_cause as hcc on hcc.ID = phc.DIRECT_CAUSE_ID group by p.ID";

		int[][] table = null;
		int xiaojiIndex = unitIDArray.indexOf(0l);
		int hejiIndex = causeIDArray.indexOf(0l);

		switch (directCauseType) {
		case Constants.HSE_CATEGORY_DANGER_ACTION:
			table = monthReport.getDangerActionTable();
			break;
		case Constants.HSE_CATEGORY_DANGER_STATUS:
			table = monthReport.getDangerStatusTable();
			break;
		default:
			return;
		}

		Connection conn = null;
		PreparedStatement tablePstm = null, dangerCountPstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();

			tablePstm = conn.prepareStatement(tableSQL);
			ResultSet tableRs = tablePstm.executeQuery();

			while (tableRs.next()) {
				Long categotyCauseID = tableRs.getLong("CATEGORY_CAUSE_ID");
				Long equipID = tableRs.getLong("EQUIPMENT_ID");
				int problemCount = tableRs.getInt("PROBLEM_COUNT");

				int unitIndex = unitIDArray.indexOf(equipID);
				int causeIndex = causeIDArray.indexOf(categotyCauseID);

				table[unitIndex][causeIndex] += problemCount;
				table[xiaojiIndex][causeIndex] += problemCount;
				table[unitIndex][hejiIndex] += problemCount;
				table[xiaojiIndex][hejiIndex] += problemCount;
			}

			dangerCountPstm = conn.prepareStatement(dangerCountSQL);
			ResultSet dangerCountRs = dangerCountPstm.executeQuery();
			int dangerProblemCount = 0;
			while (dangerCountRs.next()) {
				dangerProblemCount++;
			}
			monthReport.setDangerProblemCount(dangerProblemCount);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

	}

	public void fullfillHSEDepartMonthDetailReportProblemCount(HSEMonthDetailReport<HSEReportUnit> monthReport, String dateStart,
			String dateEnd) {
		String sql = "SELECT [ID] ,EQUIPMENT_ID  ,[WRITE_TIME], CLOSE_TIME, CLOSE_USER_ID, PROCESS_STATUS, IS_SOLVED "
				+ " FROM [problem] as p where PROBLEM_TYPE is null " + " and p.WRITE_TIME>='" + dateStart
				+ " 00:00:00' and p.WRITE_TIME<='" + dateEnd + " 23:59:59' ";
		if (monthReport.getDepartment1ID() != null) {
			sql += " and p.DEPARTMENT1_ID = " + monthReport.getDepartment1ID().longValue() + " ";
			if (monthReport.getEquipID() != null) {
				sql += " and p.EQUIPMENT_ID = " + monthReport.getEquipID().longValue() + " ";
			}
		}

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			int totalCount = 0, isSolvedCount = 0, isProvedCount = 0, notProvedCount = 0;
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				int processStatus = rs.getInt("PROCESS_STATUS");
				boolean isSolved = rs.getBoolean("IS_SOLVED");

				if (isSolved) {
					isSolvedCount++;
				} else {
					totalCount++;
					if (processStatus >= Constants.PROBLEM_STATUS_CLOSE) {
						isProvedCount++;
					} else {
						notProvedCount++;
					}
				}
			}

			monthReport.setTotalCount(totalCount);
			monthReport.setIsSolvedCount(isSolvedCount);
			monthReport.setIsProvedCount(isProvedCount);
			monthReport.setNotProvedCount(notProvedCount);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public void fullfillHSECorpMonthDetailReportProblemCount(HSEMonthDetailReport<HSEReportUnit> monthReport, String dateStart,
			String dateEnd) {
		String sql = "SELECT [ID] ,EQUIPMENT_ID  ,[WRITE_TIME], CLOSE_TIME, CLOSE_USER_ID, PROCESS_STATUS, IS_SOLVED "
				+ " FROM [problem] as p where PROBLEM_TYPE is null " + " and p.WRITE_TIME>='" + dateStart
				+ " 00:00:00' and p.WRITE_TIME<='" + dateEnd + " 23:59:59' ";
		if (monthReport.getDepartment1ID() != null) {
			sql += " and p.DEPARTMENT1_ID = " + monthReport.getDepartment1ID().longValue() + " ";
			if (monthReport.getEquipID() != null) {
				sql += " and p.EQUIPMENT_ID = " + monthReport.getEquipID().longValue() + " ";
			}
		}

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			int totalCount = 0, isSolvedCount = 0, isProvedCount = 0, notProvedCount = 0;
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				int processStatus = rs.getInt("PROCESS_STATUS");
				boolean isSolved = rs.getBoolean("IS_SOLVED");

				if (isSolved) {
					isSolvedCount++;
				} else {
					totalCount++;
					if (processStatus >= Constants.PROBLEM_STATUS_CLOSE) {
						isProvedCount++;
					} else {
						notProvedCount++;
					}
				}
			}

			monthReport.setTotalCount(totalCount);
			monthReport.setIsSolvedCount(isSolvedCount);
			monthReport.setIsProvedCount(isProvedCount);
			monthReport.setNotProvedCount(notProvedCount);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public void fullfillHSECorpMonthDetailReportTable(HSEMonthDetailReport<HSEReportUnit> monthReport, String dateStart, String dateEnd,
			int directCauseType, List<Long> unitIDArray, List<Long> causeIDArray) {
		String tableSQL = "select hcc.ID CATEGORY_CAUSE_ID, p.DEPARTMENT1_ID, COUNT(phc.PROBLEM_ID) PROBLEM_COUNT "
				+ " from problem_hse_category as phc inner join problem as p on p.ID = phc.PROBLEM_ID  " + " and p.WRITE_TIME>='"
				+ dateStart + " 00:00:00' and p.WRITE_TIME<='" + dateEnd + " 23:59:59' ";
		if (monthReport.getDepartment1ID() != null) {
			tableSQL += " and p.DEPARTMENT1_ID = " + monthReport.getDepartment1ID().longValue() + " ";
			if (monthReport.getEquipID() != null) {
				tableSQL += " and p.EQUIPMENT_ID = " + monthReport.getEquipID().longValue() + " ";
			}
		}
		tableSQL += " inner join hse_category_cause as hcc on hcc.ID = phc.DIRECT_CAUSE_ID and hcc.DIRECT_CAUSE_TYPE = " + directCauseType
				+ " group by hcc.ID, p.DEPARTMENT1_ID";

		String dangerCountSQL = "select p.ID, COUNT(*) "
				+ " from problem_hse_category as phc inner join problem as p on p.ID = phc.PROBLEM_ID  " + " and p.WRITE_TIME>='"
				+ dateStart + " 00:00:00' and p.WRITE_TIME<='" + dateEnd + " 23:59:59' ";
		if (monthReport.getDepartment1ID() != null) {
			dangerCountSQL += " and p.DEPARTMENT1_ID = " + monthReport.getDepartment1ID().longValue() + " ";
			if (monthReport.getEquipID() != null) {
				dangerCountSQL += " and p.EQUIPMENT_ID = " + monthReport.getEquipID().longValue() + " ";
			}
		}
		dangerCountSQL += " inner join hse_category_cause as hcc on hcc.ID = phc.DIRECT_CAUSE_ID   group by p.ID";

		int[][] table = null;
		int xiaojiIndex = unitIDArray.indexOf(0l);
		int hejiIndex = causeIDArray.indexOf(0l);

		switch (directCauseType) {
		case Constants.HSE_CATEGORY_DANGER_ACTION:
			table = monthReport.getDangerActionTable();
			break;
		case Constants.HSE_CATEGORY_DANGER_STATUS:
			table = monthReport.getDangerStatusTable();
			break;
		default:
			return;
		}

		Connection conn = null;
		PreparedStatement tablePstm = null, dangerCountPstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();

			tablePstm = conn.prepareStatement(tableSQL);
			ResultSet tableRs = tablePstm.executeQuery();

			while (tableRs.next()) {
				Long categotyCauseID = tableRs.getLong("CATEGORY_CAUSE_ID");
				Long depart1ID = tableRs.getLong("DEPARTMENT1_ID");
				int problemCount = tableRs.getInt("PROBLEM_COUNT");

				int unitIndex = unitIDArray.indexOf(depart1ID);
				int causeIndex = causeIDArray.indexOf(categotyCauseID);

				table[unitIndex][causeIndex] += problemCount;
				table[xiaojiIndex][causeIndex] += problemCount;
				table[unitIndex][hejiIndex] += problemCount;
				table[xiaojiIndex][hejiIndex] += problemCount;
			}

			dangerCountPstm = conn.prepareStatement(dangerCountSQL);
			ResultSet dangerCountRs = dangerCountPstm.executeQuery();
			int dangerProblemCount = 0;
			while (dangerCountRs.next()) {
				dangerProblemCount++;
			}
			monthReport.setDangerProblemCount(dangerProblemCount);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

	}

	public void fullfillHSEMonthCompareReportRemoteCauseTable(HSEMonthCompareReport<HSEReportUnit> compareReport, String dateStart,
			String dateEnd, List<Long> remoteCauseIDArray) {

		String sql = "select  hcc.ID, COUNT(*)  HSE_COUNT  from problem_hse_category as phc "
				+ " inner join problem as p on p.ID = phc.PROBLEM_ID  and p.WRITE_TIME>='" + dateStart + " 00:00:00' and p.WRITE_TIME<='"
				+ dateEnd + " 23:59:59' ";
		if (compareReport.getDepartment1ID() != null) {
			sql += " and p.DEPARTMENT1_ID = " + compareReport.getDepartment1ID().longValue() + " ";
			if (compareReport.getEquipID() != null) {
				sql += " and p.EQUIPMENT_ID = " + compareReport.getEquipID().longValue() + " ";
			}
		}

		sql += " inner join hse_category_cause as hcc on hcc.ID = phc.REMOTE_CAUSE_ID group by hcc.ID";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			int[][] remoteCauseTable = new int[remoteCauseIDArray.size()][2];
			int totalRemoteCount = 0;
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				long remoteCauseID = rs.getLong("ID");
				int hseCount = rs.getInt("HSE_COUNT");

				totalRemoteCount += hseCount;

				int remoteIndex = remoteCauseIDArray.indexOf(remoteCauseID);
				remoteCauseTable[remoteIndex][0] = hseCount;
			}

			for (int i = 0; i < remoteCauseTable.length; i++) {
				if (totalRemoteCount == 0) {
					remoteCauseTable[i][1] = 0;
				} else {
					remoteCauseTable[i][1] = (remoteCauseTable[i][0] * 10000 / totalRemoteCount);
				}
			}

			compareReport.setRemoteCauseTable(remoteCauseTable);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public List<HSECategoryCause> getHSECategoryCause(Integer causeType, Integer directCauseType) {
		String sql = "SELECT [ID] ,[CAUSE_NAME] ,[CAUSE_TYPE] ,[DIRECT_CAUSE_TYPE]  FROM [hse_category_cause] ";
		if (causeType != null) {
			sql += " where CAUSE_TYPE = " + causeType.intValue();
			if (causeType.intValue() == 1 && directCauseType != null) {
				sql += " and DIRECT_CAUSE_TYPE = " + directCauseType.intValue();
			}
		}
		sql += " order by ID asc";
		Connection conn = null;
		PreparedStatement pstm = null;
		List<HSECategoryCause> causeList = new ArrayList<HSECategoryCause>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				HSECategoryCause cause = new HSECategoryCause();
				cause.setId(rs.getLong("ID"));
				cause.setCauseName(rs.getString("CAUSE_NAME"));
				cause.setCauseType(rs.getInt("CAUSE_TYPE"));
				cause.setDirectCauseType(rs.getInt("DIRECT_CAUSE_TYPE"));

				causeList.add(cause);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return causeList;
	}

	public void addHSECompareReport(HSEMonthCompareReport<HSEReportUnit> previousCompareReport) {
		String sql = "insert into [hse_month_report] ([YEAR] ,[MONTH]  ,[DEPARTMENT1_ID]  ,[EQUIPMENT_ID]  ,[COMPARE_REPORT]) "
				+ " values(?,?,?,?,?) select @@identity as REPORT_ID";

		Connection conn = null;
		PreparedStatement pstm = null;
		Long reportID = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			pstm.setInt(1, previousCompareReport.getYear());
			pstm.setInt(2, previousCompareReport.getMonth());

			if (previousCompareReport.getDepartment1ID() == null) {
				pstm.setNull(3, Types.INTEGER);
			} else {
				pstm.setLong(3, previousCompareReport.getDepartment1ID());
			}

			if (previousCompareReport.getEquipID() == null) {
				pstm.setNull(4, Types.INTEGER);
			} else {
				pstm.setLong(4, previousCompareReport.getEquipID());
			}

			ByteArrayOutputStream out = new ByteArrayOutputStream();
			ObjectOutputStream oos = new ObjectOutputStream(out);
			oos.writeObject(previousCompareReport);
			oos.flush();
			oos.close();
			out.close();

			byte[] bytes = out.toByteArray();
			pstm.setObject(5, bytes);

			pstm.executeUpdate();
			ResultSet rs = pstm.getGeneratedKeys();
			if (rs.next()) {
				reportID = rs.getLong("REPORT_ID");
			}

			conn.commit();
			conn.setAutoCommit(true);

		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

	}

	public HSEMonthCompareReport<HSEReportUnit> getHSECompareReport(int year, int month, Long depart1ID, Long equipID,
			HSEMonthCompareReport<HSEReportUnit> previousCompareReport) {
		String sql = "select [ID]  ,[YEAR] ,[MONTH]  ,[DEPARTMENT1_ID]  ,[EQUIPMENT_ID]  ,[COMPARE_REPORT] FROM [hse_month_report] "
				+ " where YEAR = " + year + " and MONTH= " + month + " ";

		if (depart1ID == null) {
			sql += " and DEPARTMENT1_ID is null ";
		} else {
			sql += " and DEPARTMENT1_ID = " + depart1ID.longValue();
		}

		if (equipID == null) {
			sql += " and EQUIPMENT_ID is null ";
		} else {
			sql += " and EQUIPMENT_ID = " + equipID.longValue();
		}

		Connection conn = null;
		PreparedStatement pstm = null;
		ObjectInputStream in = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				Blob reportBlob = rs.getBlob("COMPARE_REPORT");

				in = new ObjectInputStream(reportBlob.getBinaryStream());

				previousCompareReport = (HSEMonthCompareReport<HSEReportUnit>) in.readObject();

				in.close();

				return previousCompareReport;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				in.close();
				conn.close();
			} catch (Exception e) {
			}
		}
		return null;
	}
}
